﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Collections.ObjectModel;
using System.Globalization;

namespace University.Workshop.DataAccess
{
    public class DataAccessTransaction
    {
        private string PathDataBase;
        private SqlConnection SQLConnection;


        public DataAccessTransaction()
        {
            this.PathDataBase = "Data Source=.\\SQLEXPRESS; Initial Catalog=TransactionDataBase; Integrated Security=true";
        }


        public bool InsertTransaction(string fromUserName, string toUserName, long amount)
        {
            bool Status = false;

            using (this.SQLConnection = new SqlConnection(this.PathDataBase))
            {
                SqlCommand SQLCommand = this.SQLConnection.CreateCommand();
                SQLCommand.CommandText = "INSERT INTO [TableTransaction] (fromUserName, toUserName, AmountTransfer, Date) VALUES(@VarFromUserName, @VarToUserName, @VarAmount, @VarDate)";
                SQLCommand.Parameters.AddWithValue("VarFromUserName", fromUserName);
                SQLCommand.Parameters.AddWithValue("VarToUserName", toUserName);
                SQLCommand.Parameters.AddWithValue("VarAmount", amount);
                SQLCommand.Parameters.AddWithValue("VarDate", System.DateTime.Now);

                try
                {
                    this.SQLConnection.Open();
                    int Result = SQLCommand.ExecuteNonQuery();

                    if (Result <= 0)
                    {
                        Status = false;
                    }
                    else
                    {
                        Status = true;
                    }
                }
                catch (SqlException)
                {
                    Status = false;
                }
                finally
                {
                    if (this.SQLConnection != null && this.SQLConnection.State == System.Data.ConnectionState.Open)
                    {
                        this.SQLConnection.Close();
                    }
                }
                return (Status);
            }
        }

        public Collection<Transaction> GetReceivedTransaction(string userName)
        {
            Collection<Transaction> TransactionList = new Collection<Transaction>();
            SqlDataReader ReaderInfo = null;

            using (this.SQLConnection = new SqlConnection(this.PathDataBase))
            {
                SqlCommand SQLCommand = this.SQLConnection.CreateCommand();
                SQLCommand.CommandText = "SELECT * FROM [TableTransaction] WHERE toUserName=@VarToUserName";
                SQLCommand.Parameters.AddWithValue("VarToUserName", userName);

                try
                {
                    this.SQLConnection.Open();
                    ReaderInfo = SQLCommand.ExecuteReader();

                    while (ReaderInfo.Read())
                    {
                        Transaction Item = new Transaction();

                        Item.FromUserName = ReaderInfo["FromUserName"].ToString();
                        Item.ToUserName = ReaderInfo["ToUserName"].ToString();
                        Item.TransactionNumber = Convert.ToInt32(ReaderInfo["TransactionNumber"].ToString(), CultureInfo.CurrentCulture);
                        Item.AmountTransfer = Convert.ToInt32(ReaderInfo["AmountTransfer"].ToString(), CultureInfo.CurrentCulture);
                        Item.Date = Convert.ToDateTime(ReaderInfo["Date"].ToString(), CultureInfo.CurrentCulture);

                        TransactionList.Add(Item);
                    }
                }
                catch (SqlException)
                {
                    TransactionList = null;
                }
                finally
                {
                    if (this.SQLConnection != null && this.SQLConnection.State == System.Data.ConnectionState.Open)
                    {
                        this.SQLConnection.Close();
                    }
                }
                return (TransactionList);
            }
        }

        public Collection<Transaction> GetSendTransaction(string userName)
        {
            Collection<Transaction> TransactionList = new Collection<Transaction>();
            SqlDataReader ReaderInfo = null;

            using (this.SQLConnection = new SqlConnection(this.PathDataBase))
            {
                SqlCommand SQLCommand = this.SQLConnection.CreateCommand();
                SQLCommand.CommandText = "SELECT * FROM [TableTransaction] WHERE fromUserName=@VarFromUserName";
                SQLCommand.Parameters.AddWithValue("VarFromUserName", userName);

                try
                {
                    this.SQLConnection.Open();
                    ReaderInfo = SQLCommand.ExecuteReader();

                    while (ReaderInfo.Read())
                    {
                        Transaction Item = new Transaction();

                        Item.FromUserName = ReaderInfo["FromUserName"].ToString();
                        Item.ToUserName = ReaderInfo["ToUserName"].ToString();
                        Item.TransactionNumber = Convert.ToInt32(ReaderInfo["TransactionNumber"].ToString(), CultureInfo.CurrentCulture);
                        Item.AmountTransfer = Convert.ToInt32(ReaderInfo["AmountTransfer"].ToString(), CultureInfo.CurrentCulture);
                        Item.Date = Convert.ToDateTime(ReaderInfo["Date"].ToString(), CultureInfo.CurrentCulture);

                        TransactionList.Add(Item);
                    }
                }
                catch (SqlException)
                {
                    TransactionList = null;
                }
                finally
                {
                    if (this.SQLConnection != null && this.SQLConnection.State == System.Data.ConnectionState.Open)
                    {
                        this.SQLConnection.Close();
                    }
                }
                return (TransactionList);
            }
        }

        public Collection<Transaction> GetBothTransaction(string userName)
        {
            Collection<Transaction> TransactionList = new Collection<Transaction>();
            SqlDataReader ReaderInfo = null;

            using (this.SQLConnection = new SqlConnection(this.PathDataBase))
            {
                SqlCommand SQLCommand = this.SQLConnection.CreateCommand();
                SQLCommand.CommandText = "SELECT * FROM [TableTransaction] WHERE fromUserName=@VarUserName OR toUserName=@VarUserName";
                SQLCommand.Parameters.AddWithValue("VarUserName", userName);

                try
                {
                    this.SQLConnection.Open();
                    ReaderInfo = SQLCommand.ExecuteReader();

                    while (ReaderInfo.Read())
                    {
                        Transaction Item = new Transaction();

                        Item.FromUserName = ReaderInfo["FromUserName"].ToString();
                        Item.ToUserName = ReaderInfo["ToUserName"].ToString();
                        Item.TransactionNumber = Convert.ToInt32(ReaderInfo["TransactionNumber"].ToString(), CultureInfo.CurrentCulture);
                        Item.AmountTransfer = Convert.ToInt32(ReaderInfo["AmountTransfer"].ToString(), CultureInfo.CurrentCulture);
                        Item.Date = Convert.ToDateTime(ReaderInfo["Date"].ToString(), CultureInfo.CurrentCulture);

                        TransactionList.Add(Item);
                    }
                }
                catch (SqlException)
                {
                    TransactionList = null;
                }
                finally
                {
                    if (this.SQLConnection != null && this.SQLConnection.State == System.Data.ConnectionState.Open)
                    {
                        this.SQLConnection.Close();
                    }
                }
                return (TransactionList);
            }
        }
    }
}
